from numpy import double

from DBUlist import select, inster


def add():  # 添加账务
    null = 0
    category = input("请输入新类别:")
    account = input("请输入新账户:")
    money = int(input("请输入新金额:"))
    time_A = input("请输入时间:")
    explain_A = input("请输入说明书:")
    sql = "INSERT INTO tally VALUE (%s,%s,%s,%s,%s,%s)"
    param = [null, category, account, money, time_A, explain_A]
    inster(sql, param)


def compile_A():  # 编辑
    ID = int(input("请输入ID:"))
    category = input("请输入新类别:")
    account = input("请输入新账户:")
    money = int(input("请输入新金额:"))
    time_A = input("请输入时间:")
    explain_A = input("请输入说明书:")
    sql = "UPDATE tally SET category =%s,account=%s, money=%s,time_A =%s,explain_A =%s WHERE ID =%s"
    param = [category, account, money, time_A, explain_A, ID]
    inster(sql, param)


def delete():
    ID = int(input("请输入ID:"))
    sql = "DELETE FROM tally WHERE ID = %s"
    param = [ID]
    inster(sql, param)
    sql = "SELECT ID FROM tally WHERE ID =%s"
    param = [ID]
    num1 = len(select(sql, param))
    if num1 == 0:
        print("删除成功")


def inquire():
    length = "{:<10}{:<15}{:<15}{:<15}{:<15}{:<10}"
    print("1、查询所有\t 2、按条件查询")
    num_A = int(input())
    if num_A == 1:
        print()
        sql = "SELECT * FROM tally"
        num3 = select(sql)
        print(length.format("ID", "类别", "账户", "金额", "时间", "说明"))
        for i in range(len(num3)):
            num3_A = num3[i][4]
            num3_A = str(num3_A)
            print(length.format(num3[i][0], num3[i][1], num3[i][2], num3[i][3], num3_A, num3[i][5]))
    elif num_A == 2:
        time_A = input("请输入查询开始时间:")
        time_B = input("请输入查询结束时间:")
        sql = "SELECT * FROM tally WHERE time_A >=%s  AND time_A <=%s"
        param = [time_A, time_B]
        num3 = select(sql, param)
        # print("ID\t\t类别\t\t\t\t账户\t\t\t\t金额\t\t\t\t\t时间\t\t\t\t\t\t说明")
        print(length.format("ID", "类别", "账户", "金额", "时间", "说明"))
        for i in range(len(num3)):
            num3_A = num3[i][4]
            num3_A = str(num3_A)
            print(length.format(num3[i][0], num3[i][1], num3[i][2], num3[i][3], num3_A, num3[i][5]))


while True:
    print("------------------------管家婆家庭记账软件---------------------")
    print("1、添加账务\t 2、编辑账务\t 3、删除账务\t 4、查询账务\t 5、退出系统")
    num = int(input("请输入要操作的功能序号[1~5]:"))
    if num == 1:
        print("1、添加账务")
        add()
    elif num == 2:
        print("2、编辑账务")
        compile_A()
    elif num == 3:
        print("3、删除账务")
        delete()
    elif num == 4:
        print("4、查询账务")
        inquire()
    elif num == 5:
        print("退出系统")
        break
